package cn.dubito.seller.repository;

import cn.dubito.entity.VerificationOrder;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;

import java.util.Date;
import java.util.List;

/**
 * @author liliang
 * @description: 对账相关
 * @date 2018/5/29.
 */
public interface VerificationRepository extends JpaRepository<VerificationOrder,String>,JpaSpecificationExecutor<VerificationOrder> {

    /**
     * 查询某段时间[start,end)内的渠道对账数据
     * @param chanId 渠道 ID
     * @param start 开始时间
     * @param end 结束时间
     * @return 对账数据列表
     */
    @Query(value = "SELECT CONCAT_WS('|',order_id,chan_id,product_id,chan_user_id,order_type,outer_order_id,amount,create_at) FROM tb_order WHERE order_status = 'SUCCESS' AND chan_id = ?1 AND create_at >= ?2 AND create_at < ?3",nativeQuery = true)
    List<String> queryVerificationOrders(String chanId, Date start,Date end);


    /**
     * 查询某段时间[start,end)内的渠道长款数据
     * @param chanId 渠道 ID
     * @param startTime 开始时间
     * @param endTime 结束时间
     * @return 对账数据列表
     */
    @Query(value = "SELECT t.order_id FROM tb_order t LEFT JOIN tb_verification_order v ON t.chan_id= ?1 AND t.outer_order_id=v.order_id WHERE v.order_id IS NULL AND t.create_at >= ?2 AND t.create_at < ?3",nativeQuery = true)
    List<String> queryExcessOrder(String chanId, Date startTime, Date endTime);

    /**
     * 查询某段时间[start,end)内的渠道漏单数据
     * @param chanId 渠道 ID
     * @param startTime 开始时间
     * @param endTime 结束时间
     * @return 对账数据列表
     */
    @Query(value = "SELECT v.order_id FROM tb_verification_order v LEFT JOIN tb_order t ON t.chan_id = ?1 AND v.outer_order_id = t.order_id WHERE t.order_id IS NULL AND v.create_at >= ?2 AND v.create_at < ?3",nativeQuery = true)
    List<String> queryMissOrder(String chanId, Date startTime, Date endTime);

    /**
     * 查询某段时间[start,end)内的渠道不一致数据
     * @param chanId 渠道 ID
     * @param startTime 开始时间
     * @param endTime 结束时间
     * @return 对账数据列表
     */
    @Query(value = "SELECT t.order_id FROM tb_order t JOIN tb_verification_order v ON t.chan_id = ?1 AND t.outer_order_id = v.order_id\n" +
            "WHERE CONCAT_WS('|',t.chan_id,t.product_id,t.chan_user_id,t.order_type,t.amount,t.create_at) !=\n" +
            "  CONCAT_WS('|',v.chan_id,v.product_id,v.chan_user_id,v.order_type,v.amount,v.create_at) AND t.create_at >= ?2 AND t.create_at < ?3",nativeQuery = true)
    List<String> queryDifferentOrder(String chanId, Date startTime, Date endTime);

}
